Data reduction through early grouping

SQL queries containing GROUP BY and aggre-gation occur frequently in decision support applications. Grouping with aggregation is typically done by rst sorting the input and then performing the aggregation as part of the output phase of the sort. The most widely used external sorting algorithm is merge sort, consisting of a run formation phase followed by a (single) merge pass. The amount of data output from the run formation phase can be reduced by a technique that we call early grouping. The idea is straightforward: simply form groups and perform aggregation during run formation. Each run will now consist of partial groups instead of individual records. These partial groups are then combined during the merge phase. Early grouping always reduces the number of records output from the run formation phase. The relative output size depends on the amount of memory relative to the total number of groups and the distribution of records over groups. When the input data is uniformly distributed | the worst case | our simulation results show that the relative output size is proportional to the (relative) amount of memory used. When the data is skewed | the more common case in practice | the relative output size is much smaller.